这是我工作遇到的问题,现在自己设计一个简化的类似场景,现实中这样的数据表设计可能有很多不合理的地方。
首先看表结构:
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | varchar(38) | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| course | varchar(300) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
这里只是记录学生的ID,名字,还有选课的科目,科目有很多,在没有关联表的情况下,这么多科目只保存在一个字段中,用逗号隔开。
再看一些数据:
+--------------------------------------+--------+--------------------------------+
| id | name | course |
+--------------------------------------+--------+--------------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |
| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五 | Math,English,Algorithm |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |
| 656a247a-f33d-11e4-a31d-089e0140e076 | 曹达华 | Chesses |
+--------------------------------------+--------+--------------------------------+
那么如何查找到选择了Math
课程的学生?
想想使用关联表的时候,张三
, 李四
, 李五
, 王小明
这四个人都一条选择了Math
这门课的记录,还有其他不是Math
的记录。此时要查找选择了Math
课程的学生,一般使用IN
语句就可以了:
select * from student_course where course IN ('Math');
如果要查找选择了Math
或Algorithm
课程的学生呢:
select * from student_course where course IN ('Math', 'Algorithm');
如此,回到原来的问题,如果我设计一个类似IN
一样的函数,那么就可以解决这个问题了。
这个流程我们可以想象出来,是这样子的:
我们取张三
的课程信息Math,English,Chinese
,首先切割成Math
, English
,Chinese
三个字段,然后分别与与查找条件做比较,类似'Math'.indexOf('Math');
,'Math'.indexOf('English');
...
只要找到一个就认为符合查找条件。
同样的,如果要查找选择了Math
或Algorithm
课程的学生,比较过程就变成了:'Math,Algorithm'.indexOf('Math');
,'Math,Algorithm'.indexOf('English');
...
切割函数 getSplitTotalLength, getSplitString
CREATE DEFINER = `root`@`%` FUNCTION `getSplitTotalLength`(`f_string` varchar(500),`f_delimiter` varchar(5))
RETURNS int(11)
BEGIN
# 计算传入字符串能切分成多少段
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
RETURN 0;
END;
CREATE DEFINER = `root`@`%` FUNCTION `getSplitString`(`f_string` varchar(500),`f_delimiter` varchar(5),`f_order` int)
RETURNS varchar(500)
BEGIN
#拆分传入的字符串,分隔符,顺序,返回拆分所得的新字符串
declare result varchar(500) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;
类似IN的那个函数 isInSearch
CREATE DEFINER=`root`@`%` FUNCTION `isInSearch`(f_course VARCHAR(300), f_string VARCHAR(300)) RETURNS INT
BEGIN
DECLARE len INT DEFAULT 0;
DECLARE idx INT DEFAULT 0;
DECLARE item_code VARCHAR(300) DEFAULT '';
DECLARE item_index INT DEFAULT 0;
IF f_course IS NULL THEN
RETURN 0;
END IF;
SELECT getSplitTotalLength(f_course, ',') INTO len;
label: LOOP
SET idx = idx + 1;
IF idx > len THEN
LEAVE label;
END IF;
SELECT getSplitString(f_course , ',', idx) INTO item_code;
# f_string.indexOf(item_code) > -1 ?
SELECT LOCATE(item_code, f_string) INTO item_index;
IF item_index > 0 THEN
RETURN 1; # got one
END IF;
END LOOP label;
RETURN 0;
END;
这里说下locate
函数,locate(item_code, f_string)
,如果item_code
是f_string
的子串,返回的结果大于0
,是item_code
在f_string
的起始下标(从1开始算起),这个一般的indexOf
函数有些不同。
mysql> select locate('Math','Math,Algorithm');
+---------------------------------+
| locate('Math','Math,Algorithm') |
+---------------------------------+
| 1 |
+---------------------------------+
mysql> select locate('Math','Chinese,Math,Algorithm');
+-----------------------------------------+
| locate('Math','Chinese,Math,Algorithm') |
+-----------------------------------------+
| 9 |
+-----------------------------------------+
mysql> select locate('Math','Chinese,Algorithm');
+------------------------------------+
| locate('Math','Chinese,Algorithm') |
+------------------------------------+
| 0 |
+------------------------------------+
可以看到isInSearch
函数返回的是INT
类似,因为MySQL
的IN
也是这样的机制。
mysql> select 'Math' in ('Math','Algorightm');
+---------------------------------+
| 'Math' in ('Math','Algorightm') |
+---------------------------------+
| 1 |
+---------------------------------+
mysql> select 'Math' in ('Chinese','Algorightm');
+------------------------------------+
| 'Math' in ('Chinese','Algorightm') |
+------------------------------------+
| 0 |
+------------------------------------+
如果存在返回1,不存在返回0。
在SELECT语句中使用自定义的函数
mysql> select * from student_course where isInSearch(course, 'Math');
+--------------------------------------+--------+--------------------------------+
| id | name | course |
+--------------------------------------+--------+--------------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |
| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五 | Math,English,Algorithm |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |
+--------------------------------------+--------+--------------------------------+
mysql> select * from student_course where isInSearch(course, 'Chinese,Japanese');
+--------------------------------------+--------+--------------------------------+
| id | name | course |
+--------------------------------------+--------+--------------------------------+
| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |
| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |
| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |
+--------------------------------------+--------+--------------------------------+
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。